import pymysql
import pandas as pd
import os

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='root',
            database='scenic',
            port=3306,
            charset='utf8'
        )

    def get_scenic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT order_id, id_no, user_name, phone, age, user_count
        FROM
        (
            SELECT 
                o.id as order_id, 
                u.id_no, 
                u.user_name, 
                u.phone,
                CASE
                    WHEN length(u.id_no)=18 THEN YEAR(now()) - cast(substr(u.id_no,7,4) as signed)
                    ELSE NULL
                END as age,
                (SELECT count(*) FROM ticket_order_user_rel WHERE order_id=o.id) as user_count
            FROM ticket_order o
            JOIN ticket_order_user_rel u ON u.order_id=o.id
            WHERE o.pay_time is not null and o.pay_time != '' and u.id_no is not null and u.id_no != ''
        ) as subquery
        WHERE user_count=1 and (age < 18 or age >=60)
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        print(df)

        # 目标目录
        directory = r'E:\528\2025.04.30\6'  # 修改为你的实际目录路径
        # 检查目录是否存在，如果不存在则创建
        if not os.path.exists(directory):
            os.makedirs(directory)
        # 完整的文件路径
        file_path = os.path.join(directory, 'scenic_data.csv')
        # 保存DataFrame到CSV文件
        df.to_csv(file_path, index=False)

if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()
##